/**
 * @author NGUYEN Van Tho
 */


package ifi.p17.model;

import ifi.p17.Constant;
import ifi.p17.Person;
import ifi.p17.view.MainView;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Observable;
import java.util.Random;

import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.AbstractTableModel;
import javax.swing.text.html.ListView;

public class Model extends AbstractTableModel implements TableModelListener{

	// register the driver 
	private static final String SQLITE_DRIVER = "org.sqlite.JDBC";
	private static final String DB_NAME = "jdbc:sqlite:address.db";
	
	
    private static final String[] names = {Constant.NAME, Constant.HOME_PHONE, Constant.WORK_PHONE, Constant.HOME_ADDRESS, Constant.WORK_ADDRESS};
    private List<Person> persons = new ArrayList<Person>();
	
    private MainView mainView;
    
    public Model(){
    	persons = this.getPersons("");
    }
    private Connection getConnection(){
    	Connection connection = null;
    	try {
			Class.forName(SQLITE_DRIVER);
			connection = DriverManager.getConnection(DB_NAME);			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block			
			e.printStackTrace();
			return null;
		} catch (SQLException e) {			
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
    	return connection;
    }
    
    public boolean insert(Person person){
    	Connection connection = getConnection();
    	if(connection == null){
    		//error handle
    		return false;
    	}
//    	String sMakeInsert = "INSERT INTO person(rowid, name, home_phone, work_phone, home_address, work_address) " +
//    						"VALUES(rowid,'Hello from the database')";
    	PreparedStatement preparedStatement;
		try {
			preparedStatement = connection.prepareStatement( "INSERT INTO " +
				    "person(name, home_phone, work_phone, home_address, work_address)" +    		    
				    "VALUES (?, ?, ?, ?, ?)");
			preparedStatement.setString(1, person.getName());
			preparedStatement.setString(2, person.getHomeTelephone());
			preparedStatement.setString(3, person.getWorkTelephone());
			preparedStatement.setString(4, person.getHomeAddress());
			preparedStatement.setString(5, person.getWorkAddress());
			preparedStatement.execute();
			preparedStatement.close();
		} catch (SQLException e) {			
			// TODO Auto-generated catch block			
			e.printStackTrace();
			return false;
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		persons.add(person);
		fireTableRowsInserted(persons.size() - 1, persons.size() - 1);
		//fireTableDataChanged();
    	return true;
    }
    
    public List<Person> getPersons(String searchName){
    	List<Person> persons= new ArrayList<Person>();
    	Connection connection = getConnection();
    	if(connection == null){
    		//error handle
    		return null;
    	}
    	PreparedStatement preparedStatement;
		try {
			preparedStatement = connection.prepareStatement( "SELECT * FROM " +
				    " person ");
//			preparedStatement.setString(1, person.getName());
//			preparedStatement.setString(2, person.getHomeTelephone());
//			preparedStatement.setString(3, person.getWorkTelephone());
//			preparedStatement.setString(4, person.getHomeAddress());
//			preparedStatement.setString(5, person.getWorkAddress());
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) { 
			    String name = resultSet.getString("name");
			    String homePhone = resultSet.getString("home_phone");
			    String workPhone = resultSet.getString("work_phone");
			    String homeAddress = resultSet.getString("home_address");
			    String workAddress = resultSet.getString("work_address");
			    Person person = new Person(name, homePhone, workPhone, homeAddress, workAddress);
			    persons.add(person);
			}			
			preparedStatement.close();
		} catch (SQLException e) {			
			// TODO Auto-generated catch block			
			e.printStackTrace();
			return null;
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}    	
    	return persons;
    }
    public void setMainView(MainView mainView) {
		this.mainView = mainView;
	}
    public void changeView(String key){
    	mainView.changeView(key);
    }
    
    public void changeToDetailAndClear(){
    	mainView.changeToDetailAndClear();
    }
    
    @Override
    public int getRowCount() {
        return persons.size();
    }

    @Override
    public int getColumnCount() {
        return names.length;
    }

    @Override
    public Object getValueAt(int row, int col) {
    	Person person = (Person) persons.get(row);
    	switch (col) {
		case 0:
			return person.getName();
		case 1:
			return person.getHomeTelephone();
		case 2:
			return person.getWorkTelephone();
		case 3:
			return person.getHomeAddress();
		case 4:
			return person.getHomeTelephone();
			
		default:
			return "";
		} 
    }

    @Override
    public void setValueAt(Object aValue, int row, int col) {
    	Person person = persons.get(row);
    	String value = (String) aValue;
    	switch (col) {
		case 0:
			person.setName(value);
			break;
		case 1:
			person.setHomeTelephone(value);
			break;
		case 2:
			person.setWorkTelephone(value);
			break;
		case 3:
			person.setHomeAddress(value);
			break;
		case 4:
			person.setHomeTelephone(value);
			break;
		default:
			break;
		}     	
        this.fireTableCellUpdated(row, col);
    }

    @Override
    public Class<?> getColumnClass(int col) {
    	 return getValueAt(0,col).getClass();
    }

    @Override
    public String getColumnName(int col) {
        return names[col];
    }

    @Override
    public boolean isCellEditable(int row, int col) {
        return col == 1;
    }
	@Override
	public void tableChanged(TableModelEvent e) {
		// TODO Auto-generated method stub
        if (e.getType() == TableModelEvent.INSERT) {
            System.out.println("");
            System.out.println(e.getFirstRow());
        }		
		
	}	
}